package boun.swe.bob.dao;


import boun.swe.bob.entities.Group;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.*;


/**
 * Created by IntelliJ IDEA.
 * User: ememory
 * Date: 27.Ara.2010
 * Time: 16:21:04
 * To change this template use File | Settings | File Templates.
 */

@Component
public class GroupDao extends GenericDaoImpl<Group, Integer> {

    private static final Logger LOG = Logger.getLogger(GroupDao.class);

    /**
     * returns group according to primary key
     * @param groupId
     * @return
     */
    public Group findGroupByGroupId(final Integer groupId) {
        return (Group) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery("select * from groups g where g.group_id= ?").addEntity("g", Group.class);
                query.setInteger(0, groupId);
                return query.uniqueResult();
            }
        });
    }

    /**
     * returns groups of user
     * @param toUserId
     * @return
     */
    public List<Group> findUserGroupsByUserId(final Integer toUserId) {
        return (List<Group>) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery("select g.* from groups g inner join group_member gm on g.group_id = gm.group_id where gm.member_user_id = ? order by g.group_id desc").addEntity("g", Group.class);
                query.setInteger(0, toUserId);
                return query.list();
            }
        });
    }

    /**
     * method for joining to a group
     * @param groupId
     * @param userId
     */
    public void JoinGroup(final int groupId, final int userId) {
        hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery("insert into group_member(group_id,member_user_id,status,join_date) values(?,?,?,?)");
                query.setInteger(0, groupId);
                query.setInteger(1, userId);
                query.setInteger(2,1);
                query.setDate(3,new Date());
                query.executeUpdate();
                return null;
            }
        });
    }

    /**
     * method for leaving the group
     * @param groupId
     * @param userId
     */
    public void LeaveGroup(final int groupId, final int userId) {
        hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery("delete from group_member where group_id=? and member_user_id=?");
                query.setInteger(0, groupId);
                query.setInteger(1, userId);
                query.executeUpdate();
                return null;
            }
        });
    }

    /**
     * returns whether user belongs to a group
     * @param groupId
     * @param userId
     * @return
     */
    public Boolean IsUserGroup(final int groupId,final int userId)
    {
        return (Boolean) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery("select g.* from groups g inner join group_member gm on g.group_id=gm.group_id where gm.group_id= ? and gm.member_user_id=?").addEntity("g", Group.class);
                query.setInteger(0, groupId);
                query.setInteger(1, userId);

                if(query.uniqueResult() != null)
                {
                    return true;
                }
                else
                {
                     return false;
                }
            }
        });
    }

    /**
     * returns groups according to a search keyword
     * @param keyword
     * @return
     */
    public List<Group> findGroupsForSearch(final String keyword) {
        return (List<Group>) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Set<Group> searchset = new HashSet<Group>();

                Query query = session.createSQLQuery("select * from groups u where u.group_name like '%" + keyword + "%' ").addEntity("u", Group.class);
                List<Group> namelist = query.list();
                searchset.addAll(namelist);

                query = session.createSQLQuery("select * from groups u where u.description like '%" + keyword + "%' ").addEntity("u", Group.class);
                List<Group> descriptionList = query.list();
                searchset.addAll(descriptionList);

                return new ArrayList<Group>(searchset);
            }
        });
    }

    /**
     * returns last inserted group
     * @param userId
     * @return
     */
    public Group findLastInsertedGroup(final int userId) {
        return (Group) hibernateTemplate.execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery("select max(group_id) from groups where owner_user_id = ?");
                query.setInteger(0, userId);
                Integer id = (Integer) query.uniqueResult();
                query = session.createSQLQuery("select * from groups where group_id = ?").addEntity(Group.class);
                query.setInteger(0, id);
                return query.uniqueResult();
            }
        });
    }


}
